﻿using Hsp.Novels.Common;
using Hsp.Novels.DbUtility;
using Hsp.Novels.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Hsp.Novels.Dal
{
    /// <summary>
    /// 章节数据服务层
    /// </summary>
    public class ChapterDal : SugarBase
    {
        #region 获取小说章节分页数据

        /// <summary>
        /// 获取小说章节分页数据
        /// </summary>
        /// <param name="paramList">查询及分页参数</param>
        /// <returns></returns>
        public static DataSet PageChapterData(Dictionary<string, string> paramList)
        {
            string strQry = "";

            #region 分页及页码数据处理处理

            var pageIndex = int.Parse(paramList["PageIndex"] ?? "1");
            var pageSize = int.Parse(paramList["PageSize"] ?? "20");
            var iMinPage = (pageIndex - 1) * pageSize + 1;
            var iMaxPage = pageIndex * pageSize;

            #endregion

            #region 参数处理

            var strNovelId = paramList.ContainsKey("NovelId") ? (paramList["NovelId"] ?? "") : "";
            if (!string.IsNullOrEmpty(strNovelId))
            {
                strNovelId = Utility.MASK(strNovelId);
                strQry += string.Format(@" AND (c.NovelId = '{0}')", strNovelId);
            }

            #endregion

            string strSql = string.Format(@"
            ;WITH PageTb AS (
                SELECT ROW_NUMBER() OVER (ORDER BY c.CreateTime DESC) RowNumber
                , c.[Id], c.[NovelId], c.[ChapterUrl], c.[NextUrl], c.[Chapter]
                , c.[ChapterIdx], c.[ChapterName], c.[HeadWord], c.[Content]
                , c.[WordCount], c.[UpdateTime], c.[CreateTime], c.[ValidChapter]
                FROM dbo.Novels n
                INNER JOIN dbo.Chapters c ON c.NovelId = n.Id
                WHERE (1 = 1){2}
            )
            SELECT * 
            FROM PageTb a
            CROSS JOIN (SELECT MAX(RowNumber) AS RecordCount FROM PageTb) AS b 
            WHERE (a.RowNumber BETWEEN {0} AND {1});
            ", iMinPage, iMaxPage, strQry);
            return DbHelperSql.Query(strSql);
        }

        /// <summary>
        /// 获取小说章节分页数据
        /// </summary>
        /// <param name="paramList">查询及分页参数</param>
        /// <returns></returns>
        public static List<Chapters> PageChapterList(Dictionary<string, string> paramList)
        {
            string strQry = "";

            #region 分页及页码数据处理处理

            var pageIndex = int.Parse(paramList["PageIndex"] ?? "1");
            var pageSize = int.Parse(paramList["PageSize"] ?? "20");
            var iMinPage = (pageIndex - 1) * pageSize + 1;
            var iMaxPage = pageIndex * pageSize;

            #endregion

            #region 参数处理

            var strNovelId = paramList.ContainsKey("NovelId") ? (paramList["NovelId"] ?? "") : "";
            if (!string.IsNullOrEmpty(strNovelId))
            {
                strNovelId = Utility.MASK(strNovelId);
                strQry += string.Format(@" AND (c.NovelId = '{0}')", strNovelId);
            }

            #endregion

            string strSql = string.Format(@"
            ;WITH PageTb AS (
                SELECT ROW_NUMBER() OVER (ORDER BY c.ChapterIdx DESC) RowNumber
                , c.[Id], c.[NovelId], c.[ChapterUrl], c.[NextUrl], c.[Chapter]
                , c.[ChapterIdx], c.[ChapterName], c.[HeadWord], c.[Content]
                , c.[WordCount], c.[UpdateTime], c.[CreateTime], c.[ValidChapter]
                FROM dbo.Novels n
                INNER JOIN dbo.Chapters c ON c.NovelId = n.Id
                WHERE (1 = 1){2}
            )
            SELECT * 
            FROM PageTb a
            CROSS JOIN (SELECT MAX(RowNumber) AS RecordCount FROM PageTb) AS b 
            WHERE (a.RowNumber BETWEEN {0} AND {1});
            ", iMinPage, iMaxPage, strQry);
            return db.Ado.SqlQuery<Chapters>(strSql);
        }

        #endregion

        #region 根据小说编号获取小说章节数据

        /// <summary>
        /// 根据小说编号获取小说章节数据
        /// </summary>
        /// <param name="novelId">小说编号</param>
        /// <returns></returns>
        public static DataSet ChapterData(string novelId)
        {
            /*string strSql = string.Format(@"
            SELECT * FROM dbo.Chapters WHERE (NovelId = '{0}') ORDER BY ChapterIdx;", novelId);*/
            string strSql = string.Format(@"
            DECLARE @NovelUrl NVARCHAR(255);
            SELECT @NovelUrl = NovelUrl FROM dbo.Novels WHERE (Id = '{0}');
            SELECT [Id],[NovelId],[ChapterUrl],[NextUrl],[Chapter],[ChapterIdx]
            ,[ChapterName],[HeadWord],[Content],[WordCount],[UpdateTime]
            ,[CreateTime],[ValidChapter] FROM dbo.Chapters 
            WHERE (NovelId = '{0}') 
            ORDER BY REPLACE(ChapterUrl, @NovelUrl, ''), ChapterIdx;", novelId);

            return DbHelperSql.Query(strSql);
        }

        /// <summary>
        /// 根据小说编号获取小说章节数据
        /// </summary>
        /// <param name="novelId">小说编号</param>
        /// <returns></returns>
        public static List<Chapters> ChapterList(string novelId)
        {
            /*string strSql = string.Format(@"
            SELECT * FROM dbo.Chapters WHERE (NovelId = '{0}') ORDER BY ChapterIdx;", novelId);*/
            string strSql = string.Format(@"
            DECLARE @NovelUrl NVARCHAR(255);
            SELECT @NovelUrl = NovelUrl FROM dbo.Novels WHERE (Id = '{0}');
            SELECT [Id],[NovelId],[ChapterUrl],[NextUrl],[Chapter],[ChapterIdx]
            ,[ChapterName],[HeadWord],[Content],[WordCount],[UpdateTime]
            ,[CreateTime],[ValidChapter] FROM dbo.Chapters 
            WHERE (NovelId = '{0}') 
            ORDER BY REPLACE(ChapterUrl, @NovelUrl, ''), ChapterIdx;", novelId);

            return db.Ado.SqlQuery<Chapters>(strSql);
        }

        /// <summary>
        /// 根据小说编号及章节地址获取小说章节数据
        /// </summary>
        /// <param name="novelId">小说编号</param>
        /// <param name="chapterUrl">章节地址</param>
        /// <returns></returns>
        public static DataSet ChapterData(string novelId, string chapterUrl)
        {
            string strSql = string.Format(@"
            SELECT [Id],[NovelId],[ChapterUrl],[NextUrl],[Chapter],[ChapterIdx],[ChapterName],[HeadWord],[Content],[WordCount],[UpdateTime],[CreateTime],[ValidChapter] FROM dbo.Chapters WHERE (NovelId = '{0}') AND (ChapterUrl = '{1}') ORDER BY ChapterIdx;", novelId, chapterUrl);
            return DbHelperSql.Query(strSql);
        }

        /// <summary>
        /// 根据小说编号及章节地址获取小说章节数据
        /// </summary>
        /// <param name="novelId">小说编号</param>
        /// <param name="chapterUrl">章节地址</param>
        /// <returns></returns>
        public static Chapters ChapterModel(string novelId, string chapterUrl)
        {
            //string strSql = string.Format(@"
            //SELECT [Id],[NovelId],[ChapterUrl],[NextUrl],[Chapter],[ChapterIdx],[ChapterName],[HeadWord],[Content],[WordCount],[UpdateTime],[CreateTime],[ValidChapter] FROM dbo.Chapters WHERE (NovelId = '{0}') AND (ChapterUrl = '{1}') ORDER BY ChapterIdx;", novelId, chapterUrl);
            //return DbHelperSql.Query(strSql);
            return db.Queryable<Chapters>().First(c => c.NovelId == novelId && c.ChapterUrl == chapterUrl);
        }

        public static DataSet ChapterDataByChapterId(string chapterId)
        {
            string strSql = string.Format(@"
            SELECT [Id],[NovelId],[ChapterUrl],[NextUrl],[Chapter],[ChapterIdx],[ChapterName],[HeadWord],[Content],[WordCount],[UpdateTime],[CreateTime],[ValidChapter] FROM dbo.Chapters WHERE (Id = '{0}');", chapterId);
            return DbHelperSql.Query(strSql);
        }

        public static Chapters ChapterModelByChapterId(string chapterId)
        {
            return db.Queryable<Chapters>().First(c => c.Id == chapterId);
        }

        #endregion

        #region 添加章节数据

        /// <summary>
        /// 添加章节数据
        /// </summary>
        /// <remarks>创建人：李海玉   创建时间：2018-06-02</remarks>
        /// <param name="model">章节实体</param>
        /// <returns></returns>
        public static int Add(Chapters model)
        {
            /*
            string strSql = string.Format
                (@"INSERT INTO Chapters
                    (NovelId, ChapterUrl, NextUrl, Chapter, ChapterIdx, ChapterName, HeadWord, Content, WordCount) 
                    VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}');"
                 , model.NovelId, model.ChapterUrl, model.NextUrl, model.Chapter, model.ChapterIdx
                 , model.ChapterName, model.HeadWord, model.Content, model.WordCount);*/

            //string strSql = string.Format
            //    (@"INSERT INTO Chapters
            //        (NovelId, ChapterUrl, NextUrl, Chapter, Content, WordCount, ChapterIdx) 
            //        VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}');"
            //     , model.NovelId, model.ChapterUrl, model.NextUrl, model.Chapter, model.Content
            //     , model.WordCount, model.ChapterIdx);

            //return DbHelperSql.ExecuteSql(strSql);

            return db.Insertable(model).ExecuteCommand();
        }

        #endregion

        //SELECT     TOP (200) Id, NovelId, ChapterUrl, NextUrl, Chapter, ChapterIdx, ChapterName, HeadWord, Content, WordCount, UpdateTime
        //FROM         Chapters

        #region 编辑章节数据

        /// <summary>
        /// 编辑章节数据
        /// </summary>
        /// <param name="model">章节实体</param>
        /// <returns></returns>
        public static int Edit(Chapters model)
        {
            //string strSql = string.Format
            //    (@"UPDATE Chapters SET Chapter='{1}', Content='{2}', WordCount='{3}', ValidChapter = '{4}'
            //         WHERE (Id = '{0}');"
            //        , model.Id, model.Chapter, model.Content, model.Content.Length, model.ValidChapter);
            //return DbHelperSql.ExecuteSql(strSql);
            return db.Updateable(model).ExecuteCommand();
        }

        #endregion

        #region 删除章节数据

        /// <summary>
        /// 删除章节数据
        /// </summary>
        /// <param name="id">章节编号</param>
        /// <returns></returns>
        public static int Delete(string id)
        {
            //string strSql = string.Format(@"DELETE FROM dbo.Chapters WHERE (Id = '{0}');", id);
            //return DbHelperSql.ExecuteSql(strSql);

            return db.Deleteable<Chapters>().Where(n => n.Id == id).ExecuteCommand();
        }

        #endregion

        #region 批量删除章节

        /// <summary>
        /// 批量删除章节
        /// </summary>
        /// <param name="ids">章节编号集合</param>
        /// <returns></returns>
        public static int BatchDelete(string ids)
        {
            ids = ids.Trim().Replace(" ", "").Replace(",", "','");
            string strSql = string.Format(@"DELETE FROM dbo.Chapters WHERE (Id IN ('{0}'));", ids);
            return DbHelperSql.ExecuteSql(strSql);
        }

        #endregion





        #region 清空小说数据数据

        /// <summary>
        /// 清空小说数据数据
        /// </summary>
        /// <param name="novelId">小说编号</param>
        /// <returns></returns>
        public static int ClearData(string novelId)
        {
            string strSql = string.Format(@"DELETE FROM dbo.Chapters WHERE (NovelId = '{0}');", novelId);
            return DbHelperSql.ExecuteSql(strSql);
        }

        #endregion


    }
}